package com.authine.cloudpivot.ext.controller;

import ch.ethz.ssh2.ChannelCondition;
import ch.ethz.ssh2.Connection;
import ch.ethz.ssh2.Session;
import ch.ethz.ssh2.StreamGobbler;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.authine.cloudpivot.engine.api.facade.BizObjectFacade;
import com.authine.cloudpivot.engine.api.facade.FileStoreFacade;
import com.authine.cloudpivot.engine.api.model.runtime.BizObjectCreatedModel;
import com.authine.cloudpivot.engine.api.model.runtime.BizObjectModel;
import com.authine.cloudpivot.engine.api.model.runtime.BizObjectQueryModel;
import com.authine.cloudpivot.engine.api.model.runtime.SelectionValue;
import com.authine.cloudpivot.engine.api.model.system.SystemSettingModel;
import com.authine.cloudpivot.engine.api.utils.FileHelper;
import com.authine.cloudpivot.engine.component.query.api.FilterExpression;
import com.authine.cloudpivot.engine.component.query.api.Page;
import com.authine.cloudpivot.engine.component.query.api.helper.PageableImpl;
import com.authine.cloudpivot.engine.component.query.api.helper.Q;
import com.authine.cloudpivot.engine.domain.organization.Department;
import com.authine.cloudpivot.engine.enums.type.DefaultPropertyType;
import com.authine.cloudpivot.engine.enums.type.FileUploadType;
import com.authine.cloudpivot.engine.enums.type.SettingType;
import com.authine.cloudpivot.engine.service.organization.DepartmentService;
import com.authine.cloudpivot.ext.utils.ExcelUtils;
import com.authine.cloudpivot.ext.utils.SchemaUtils;
import com.authine.cloudpivot.web.api.controller.base.BaseController;
import com.authine.cloudpivot.web.api.view.ResponseResult;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
 * @Author hxd
 * @Date 2022/2/24 17:08
 * @Description
 **/
@RestController
@RequestMapping("/api/scoring")
@Slf4j
@Transactional
public class ScoringController extends BaseController {

    @Autowired
    private FileStoreFacade fileStoreFacade;

    @PersistenceContext
    EntityManager entityManager;
    @Autowired
    RedisTemplate redisTemplate;

    private static Map<String, String> ssh2ConfigMap = new HashMap<>();

    /**
     * 批量下载入库课程文件
     */
    @PostMapping("/batchDownloadCourseWarehousing")
    public ResponseResult batchDownloadCourseWarehousing(@RequestBody Map<String, Object> map) {

        List<String> bizIdList = (List<String>) map.get("bizIdList");
        Boolean isIndcludeHistory = (Boolean) map.get("isIndcludeHistory");
        return batchDownloadFile(bizIdList, isIndcludeHistory, "kcqd", "kcsb_all", "kcqdCourseStructures");
    }


    /**
     * 批量下载申报课程文件
     */
    @PostMapping("/batchDownloadCourseFile")
    public ResponseResult batchDownloadCourseFile(@RequestBody List<String> bizIdList) {


        return batchDownloadFile(bizIdList, false, "kcsbNew", "kcsb_all", "courseStructuresNew");

    }

    private ResponseResult batchDownloadFile(List<String> bizIdList, Boolean isIndcludeHistory, String schemaCode, String queryCode, String structuresCode) {
        if (CollectionUtils.isEmpty(bizIdList)) {
            return getErrResponseResult(-1L, "请求参数不能为空");
        }

        if (bizIdList.size() > 100) {
            return getErrResponseResult(-2L, "导出文件过大,不要超过100条,请分批导出");
        }

        BizObjectQueryModel queryModel = new BizObjectQueryModel();
        queryModel.setSchemaCode(schemaCode);
        //这个视图显示字段要全,否则获取不到相关类容
        queryModel.setQueryCode(queryCode);
        queryModel.setFilterExpr(Q.it("id", FilterExpression.Op.In, bizIdList));
        queryModel.setPageable(new PageableImpl(0, 200));
        Page<BizObjectModel> page = getBizObjectFacade().queryBizObjects(queryModel);
        if (page.getTotal() == 0) {
            return getErrResponseResult(-1L, "课程信息不存在");
        }

        Map<String, Object> historyInfo = null;
        if (BooleanUtils.toBoolean(isIndcludeHistory)) {
            historyInfo = getHistoryCourse(page);
        }

        //生成说明文档
        Map<String, String> courseDescMap = page.getContent().stream()
                .collect(Collectors.toMap(biz -> biz.getString("courseName"), biz -> generateCourseDescriptionFile(biz, structuresCode), (k1, k2) -> k1));

        //获取课程附件原数据
        Map<String, Map<String, Map<String, String>>> courseFile = page.getContent().stream()
                .collect(Collectors.toMap(biz -> biz.getString("kcbh").concat(";;").concat(biz.getString("courseName")), biz -> getCourseAttachment(biz), (k1, k2) -> k1));


        //编写sh脚本
        ResponseResult responseResult = executeZip(courseFile, courseDescMap, historyInfo);

        if (responseResult.getErrcode() == 0) {

            BizObjectCreatedModel batchdownload = new BizObjectCreatedModel("batchdownload", new HashMap<>(), false);
            batchdownload.put("course", courseFile.keySet().stream().collect(Collectors.joining(";")));
            batchdownload.put("downloadPath", responseResult.getErrmsg());
            getBizObjectFacade().saveBizObject(getUserId(), batchdownload, false);
        }
        return responseResult;
    }


    /**
     * 获取历史课程信息
     */
    private Map<String, Object> getHistoryCourse(Page<BizObjectModel> cousrePage) {

        Map<String, Object> map = new HashMap<>();

        List<String> kcbhList = cousrePage.getContent().stream().map(biz -> biz.getString("kcbh")).filter(StringUtils::isNotEmpty).collect(Collectors.toList());

        BizObjectQueryModel queryModel = new BizObjectQueryModel();
        queryModel.setSchemaCode("kcqdHistory");
        //这个视图显示字段要全,否则获取不到相关类容
        queryModel.setQueryCode("kcqdLs_all");
        queryModel.setFilterExpr(Q.it("kcbh", FilterExpression.Op.In, kcbhList));
        queryModel.setPageable(new PageableImpl(0, 200));
        Page<BizObjectModel> page = getBizObjectFacade().queryBizObjects(queryModel);
        if (page.getTotal() == 0) {
            log.info("历史课程信息不存在");
        }

        //按课程名称分组
        Map<String, ? extends List<? extends BizObjectModel>> kcbh = page.getContent().stream().collect(Collectors.groupingBy(biz -> biz.getString("kcbh")));
        Map<String, Map<String, Map<String, Map<String, String>>>> courseFile = new HashMap<>();

        kcbh.forEach((k, v) -> {

            List<Map<String, Object>> collect = v.stream().map(biz -> {
                //说明文档 todo 后期优化历史课程说明文档 保存到字段里,无需每次都生成
                String descFileName = generateCourseDescriptionFile(biz, "kcqdCourseStructuresLs");
                //附件
                Map<String, Map<String, String>> courseAttachment = getCourseAttachment(biz);

                Map<String, Object> map1 = new HashMap<>();

                map1.put("courseDesc", descFileName);
                map1.put("courseFile", courseAttachment);
                map1.put("createdTime", DateFormatUtils.format(biz.getCreatedTime(), "yyyyMMddHHmmss"));
                map1.put("courseName", biz.getString("courseName"));
                return map1;

            }).collect(Collectors.toList());

            map.put(k, collect);
        });


        return map;
    }




    private Map<String, String> getSsh2SessionConfig() {
        String key = "ssh2Sessioninfotime";
        Boolean ssh2Sessioninfotime = redisTemplate.hasKey(key);

        if (MapUtils.isEmpty(ssh2ConfigMap) || !ssh2Sessioninfotime) {
            List<SystemSettingModel> systemSettings = super.getSystemManagementFacade().getSystemSettingsBySettingType(SettingType.FILE_UPLOAD);
            ssh2ConfigMap = systemSettings.stream()
                    .filter((t) -> FileUploadType.FTP.equals(t.getFileUploadType()) && t.getChecked())
                    .collect(Collectors.toMap(SystemSettingModel::getSettingCode, SystemSettingModel::getSettingValue, (k1, k2) -> k1));
            //每12 小时重新获取 配置
            redisTemplate.opsForValue().set(key,"1",24, TimeUnit.HOURS);
            if (log.isDebugEnabled()) {
                log.debug("加载SFTP配置：{}", JSON.toJSONString(ssh2ConfigMap));
            }
        }

        return ssh2ConfigMap;
    }


    public ResponseResult executeZip(Map<String, Map<String, Map<String, String>>> courseFileInfo, Map<String, String> courseDescMap, Map<String, Object> historyInfo) {
        //创建文件目录
        String sourcePath = "/data/sftp/";
        String basePath = "/data/cloudpivot/courseFile/";
        String filePath = "课程附件".concat(DateFormatUtils.format(new Date(), "yyyyMMddHHmmss"));
        String currentPath = basePath + filePath;
        String zipFile = filePath.concat(".zip");

        StringBuilder command = new StringBuilder("mkdir -p ").append(currentPath);
        List<String> commandList = new ArrayList<>();

        commandList.add(command.toString());


        courseFileInfo.forEach((kcbhAndcourseName, value) -> {
            String[] split = kcbhAndcourseName.split(";;");
            String kcbh = split[0];
            String courseName = split[1];

            StringBuilder kcPath = new StringBuilder(currentPath).append("/\"").append(courseName).append("\"");

            commandList.add("mkdir ".concat(kcPath.toString()));

            //移动说明文档
            commandList.add(new StringBuilder("mv ").append(sourcePath).append(courseDescMap.get(courseName))
                    .append(" ").append(kcPath).append("/").toString());
            //生成复制附件脚本
            value.forEach((attachName, value1) -> {
                StringBuilder kcFilePath = new StringBuilder(kcPath).append("/").append(attachName);
                commandList.add("mkdir ".concat(kcFilePath.toString()));

                int index = 0;
                for (final Map.Entry<String, String> entry : value1.entrySet()) {
                    String refId = entry.getKey();
                    String fileName = entry.getValue();
                    StringBuilder fname = new StringBuilder(attachName).append("_\"").append(courseName).append("\"");
                    if (index > 0) {
                        fname.append("_").append(index);
                    }
                    fname.append(fileName.substring(fileName.lastIndexOf(".")));
                    index++;
                    commandList.add(new StringBuilder("cp -r ").append(sourcePath).append(refId).append("\"").append(fileName).append("\" ")
                            .append(kcFilePath).append("/").append(fname).toString());
                }
            });
            //操作历史课程脚本
            if (MapUtils.isNotEmpty(historyInfo)) {
                commandList.addAll(historyScript(historyInfo, kcbh, sourcePath, kcPath.toString()));
            }

        });

        //压缩
        commandList.add("cd ".concat(basePath));
        commandList.add("zip -q -r  ".concat(zipFile).concat(" ").concat(filePath));
        commandList.add("rm -rf  ".concat(currentPath));

//        List<String> list = executeFlow(commandList);
//        if (list == null) {
//            return null;
//        }
        ResponseResult result = executeSsh3(commandList);


        log.info("执行脚本后返回结果:{}", JSON.toJSONString(result));

        log.info("压缩文件:{}", zipFile);


        if (result.getErrcode() == 0) {
            result.setErrmsg("/courseFile/".concat(zipFile));
        }

        return result;
    }

    private List<String> historyScript(Map<String, Object> historyCourse, String kcbh, String sourcePath, String targetPath) {
        List<String> scriptList = new ArrayList<>();
        List<Map<String, Object>> list = (List<Map<String, Object>>) historyCourse.get(kcbh);

        if (CollectionUtils.isEmpty(list)) {
            return scriptList;
        }

        list.forEach(m -> {
            //创建目录
            String createdTime = m.get("createdTime").toString();
            String courseName = m.get("courseName").toString();
            StringBuilder historyOne = new StringBuilder(targetPath).append("/").append(createdTime);
            scriptList.add(new StringBuilder("mkdir ").append(historyOne).toString());
            historyOne.append("/");

            //移动 说明文档
            scriptList.add(new StringBuilder("mv ").append(sourcePath).append(m.get("courseDesc"))
                    .append(" ").append(historyOne).toString());
            //复制 附件
            Map<String, Map<String, String>> courseFile = (Map<String, Map<String, String>>) m.get("courseFile");

            //生成复制附件脚本
            if (MapUtils.isNotEmpty(courseFile)) {
                courseFile.forEach((attachName, value1) -> {
                    StringBuilder kcFilePath = new StringBuilder(historyOne).append(attachName);
                    scriptList.add("mkdir ".concat(kcFilePath.toString()));

                    int index = 0;
                    for (final Map.Entry<String, String> entry : value1.entrySet()) {
                        String refId = entry.getKey();
                        String fileName = entry.getValue();
                        StringBuilder fname = new StringBuilder(attachName).append("_\"").append(courseName).append("\"");
                        if (index > 0) {
                            fname.append("_").append(index);
                        }
                        fname.append(fileName.substring(fileName.lastIndexOf(".")));
                        index++;
                        scriptList.add(new StringBuilder("cp -r ").append(sourcePath).append(refId).append("\"").append(fileName).append("\" ")
                                .append(kcFilePath).append("/").append(fname).toString());
                    }


                });
            }


        });

        return scriptList;
    }


    private ResponseResult executeSsh3(List<String> commands) {

        if (log.isDebugEnabled()) {
            log.debug("ssh commands:{}", commands);
        }

        Connection conn = null;
        Session session = null;
        InputStream stdOut = null;
        InputStream stdErr = null;

        try {
            Map<String, String> settingMap = getSsh2SessionConfig();

            int port = Integer.parseInt(settingMap.get("ftpPort"));
            String host = settingMap.get("ftpServer");
            String username = settingMap.get("ftpAccount");
            String password = settingMap.get("ftpPassword");


            // 创建连接
            conn = new Connection(host, port);

            // 启动连接
            conn.connect();
            // 验证用户密码
            boolean isAuthenticated = conn.authenticateWithPassword(username, password);
            ///是否登录成功
            if (isAuthenticated == false) {
                throw new IOException("ssh Authentication failed.");
            }
            session = conn.openSession();
            // 建立虚拟终端
            session.requestPTY("bash");
            // 打开一个Shell
            session.startShell();
            stdOut = new StreamGobbler(session.getStdout());
            stdErr = new StreamGobbler(session.getStderr());
            BufferedReader stdoutReader = new BufferedReader(new InputStreamReader(stdOut));
            BufferedReader stderrReader = new BufferedReader(new InputStreamReader(stdErr));
            // 准备输入命令
            PrintWriter out = new PrintWriter(session.getStdin());
            // 输入待执行命令
            for (String line : commands) {
                out.println(line);
            }
            out.println("exit");

            // 6. 关闭输入流
            out.close();
            // 7. 等待，除非1.连接关闭；2.输出数据传送完毕；3.进程状态为退出；4.超时
            session.waitForCondition(ChannelCondition.CLOSED | ChannelCondition.EOF | ChannelCondition.EXIT_STATUS, 30000);
            log.info("Here is the output from stdout:");
            List<String> outList = new ArrayList<>();
            String line = null;
            while ((line = stdoutReader.readLine()) != null) {
                outList.add(line);
                if (log.isDebugEnabled()) {
                    log.debug("\n获取执行结果:{}", line);
                }
            }
            log.info("Here is the output from stderr:");

            List<String> errList = new ArrayList<>();
            while ((line = stderrReader.readLine()) != null) {
                errList.add(line);
                if (log.isDebugEnabled()) {
                    log.debug("\n获取执行失败结果:{}", line);
                }
            }

            if (CollectionUtils.isEmpty(errList)) {
                return getOkResponseResult(StringUtils.join(outList, ";"));
            } else {
                return getErrResponseResult(-1L, StringUtils.join(errList, ";"));
            }

        } catch (Exception e) {
            log.info(e.getMessage(), e);
            return getErrResponseResult(-1L, "执行sh 脚本失败".concat(e.getMessage()));

        } finally {
            if (session != null) {
                session.close();
            }
            if (conn != null) {
                conn.close();
            }
            if (stdOut != null) {
                try {
                    stdOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (stdErr != null) {
                try {
                    stdErr.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }


    private ResponseResult executeSsh2(List<String> commands) {

        Connection conn = null;
        Session session = null;
        try {
            Map<String, String> settingMap = getSsh2SessionConfig();

            int port = Integer.parseInt(settingMap.get("ftpPort"));
            String host = settingMap.get("ftpServer");
            String username = settingMap.get("ftpAccount");
            String password = settingMap.get("ftpPassword");


            // 创建连接
            conn = new Connection(host, port);

            // 启动连接
            conn.connect();
            // 验证用户密码
            boolean isAuthenticated = conn.authenticateWithPassword(username, password);
            ///是否登录成功
            if (isAuthenticated == false) {
                throw new IOException("Authentication failed.");
            }
            session = conn.openSession();
            //解决中文乱码问题

            String commandStr = StringUtils.join(commands, " ; ");
            session.execCommand(commandStr);


            // 消费所有输入流
            String inStr = consumeInputStream(session.getStdout());
            log.info("=======执行sh 命令返回结果:{}", inStr);
            String errStr = consumeInputStream(session.getStderr());
            log.info("=======执行sh 命令返回err结果:{}", errStr);

            if (StringUtils.isEmpty(errStr)) {
                return getOkResponseResult(inStr);
            } else {
                return getErrResponseResult(-1L, errStr);
            }

        } catch (Exception e) {
            log.info(e.getMessage(), e);
            return getErrResponseResult(-1L, "执行sh 脚本失败".concat(e.getMessage()));

        } finally {
            if (session != null) {
                session.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

    }


    /**
     * 消费inputstream，并返回
     */
    public static String consumeInputStream(InputStream is) throws IOException {
        BufferedReader br = new BufferedReader(new InputStreamReader(is));
        String s;
        StringBuilder sb = new StringBuilder();
        while ((s = br.readLine()) != null) {
            System.out.println(s);
            sb.append(s);
        }
        return sb.toString();
    }

    public List<String> executeFlow(List<String> commands) {
        log.info("=========准备执行脚本:{}", JSON.toJSONString(commands));
        List<String> rspList = new ArrayList<String>();
/*        Runtime run = Runtime.getRuntime();

        try {
            Process proc = run.exec("/bin/bash", null, null);
            BufferedReader in = new BufferedReader(new InputStreamReader(proc.getInputStream()));
            PrintWriter out = new PrintWriter(new BufferedWriter(new OutputStreamWriter(proc.getOutputStream())), true);
            for (String line : commands) {
                out.println(line);
            }
            out.println("exit");//这个命令必须执行，否则in流不结束。
            String rspLine = "";
            while ((rspLine = in.readLine()) != null) {
                log.info("=============java 调用 bash 返回结果:{}",rspLine);
                rspList.add(rspLine);
            }
            proc.waitFor();
            in.close();
            out.close();
            proc.destroy();
        } catch (IOException e1) {
            log.info("=============java 调用 bash 失败1");
            e1.printStackTrace();
            return null;
        } catch (InterruptedException e) {
            log.info("=============java 调用 bash 失败2");
            e.printStackTrace();
            return  null;
        }*/
        return rspList;
    }


    public Map<String, Map<String, String>> getCourseAttachment(BizObjectModel biz) {
        Map<String, Map<String, String>> kcFileInfo = new HashMap<>(4);
//        //课程封面
//        List<Map<String,Object>> kcfm = (List<Map<String, Object>>) biz.get("kcfm");
        //课件（附件）
        List<Map<String, Object>> sjfj = (List<Map<String, Object>>) biz.get("sjfj");
        //讲师手册
        List<Map<String, Object>> jssc = (List<Map<String, Object>>) biz.get("jssc");

//        if (CollectionUtils.isNotEmpty(kcfm)) {
//            Map<String, String> kcfmFile = kcfm.stream().collect(Collectors.toMap(m -> String.valueOf(m.get("refId")),
//                    m -> String.valueOf(m.get("name")), (k1, k2) -> k1));
//            kcFileInfo.put("课程封面",kcfmFile);
//        }

        if (CollectionUtils.isNotEmpty(sjfj)) {
            Map<String, String> sjfjFile = sjfj.stream().collect(Collectors.toMap(m -> String.valueOf(m.get("refId")),
                    m -> String.valueOf(m.get("name")), (k1, k2) -> k1));
            kcFileInfo.put("课件", sjfjFile);
        }

        if (CollectionUtils.isNotEmpty(jssc)) {
            Map<String, String> jsscFile = jssc.stream().collect(Collectors.toMap(m -> String.valueOf(m.get("refId")),
                    m -> String.valueOf(m.get("name")), (k1, k2) -> k1));
            kcFileInfo.put("讲师手册", jsscFile);
        }

        return kcFileInfo;
    }

    @RequestMapping("/generateCourseDescriptionFile")
    public ResponseResult getfill(String bizId) {
        BizObjectCreatedModel bizObject = getBizObjectFacade().getBizObject("kcsbNew", bizId);

        generateCourseDescriptionFile(bizObject, "courseStructuresNew");

        return null;
    }

    /**
     * 构建说明文档
     * 课程申报-课程结构编码 courseStructuresNew
     * 入库课程清单-课程结构编码 kcqdCourseStructures
     * 历史入库课程-课程结构编码 kcqdCourseStructuresLs
     */
    public String generateCourseDescriptionFile(BizObjectModel bizObject, String structuresCode) {
        String courseName = null;
        ByteArrayInputStream inputStream = null;
        try {


            courseName = bizObject.getString("courseName").concat(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"))).concat(".xls");

            byte[] bytes = generateCourseDescription(bizObject, structuresCode);

            if (bytes == null) {
                throw new RuntimeException("生成课程说明文档失败bytes=null");
            }

            inputStream = new ByteArrayInputStream(bytes);

            fileStoreFacade.fileUpload(courseName, inputStream);
            log.info("上传课程说明文档成功={}", courseName);


        } catch (Exception e) {
            log.info(StrUtil.format("上传课程说明文档失败={}", bizObject.getId()), e);

            throw new RuntimeException("生成课程说明文档失败");

        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return courseName;
    }

    /**
     * 构建说明文档
     * 课程申报-课程结构编码 courseStructuresNew
     * 入库课程清单-课程结构编码 kcqdCourseStructures
     * 历史入库课程-课程结构编码 kcqdCourseStructuresLs
     */
    private byte[] generateCourseDescription(BizObjectModel biz, String structuresCode) throws IOException {
        byte[] bytes = null;
        HSSFWorkbook wb = null;
        ByteArrayOutputStream outputStream = null;
        InputStream resourceAsStream = null;
        try {

            //获取模板路径
//            String tempPath = Thread.currentThread().getContextClassLoader().getResource("").getPath().concat("/templates/courseDescriptionTemp.xlsx");

            resourceAsStream = this.getClass().getResourceAsStream("/templates/courseDescriptionTemp.xls");

//            fileInputStream = new FileInputStream(tempPath);

            //读取excel模板,创建excel对象
            wb = new HSSFWorkbook(resourceAsStream);

            //读取sheet对象
            Sheet sheet = wb.getSheetAt(0);


            //第三行数据填充
            Row row = sheet.getRow(2);
            //课程名称
            row.getCell(0).setCellValue(biz.getString("courseName"));
            //课程分类
            row.getCell(1).setCellValue(biz.getString("modality"));
            //课程系列
            row.getCell(2).setCellValue(biz.getString("series"));
            // 课程类别
            row.getCell(3).setCellValue(biz.getString("category"));
            // 课程属性
            row.getCell(4).setCellValue(biz.getString("kcxl"));
            // 培训对象
            row.getCell(5).setCellValue(biz.getString("cultivateObj"));
            // 课时（45分钟/课时）
            row.getCell(6).setCellValue(biz.getString("classHour"));
            // 课程时长（分钟）
            row.getCell(7).setCellValue(biz.getString("sc"));
            // 培训最佳人数
            row.getCell(8).setCellValue(biz.getString("num"));
            // 可开放范围
            row.getCell(9).setCellValue(biz.getString("bmkffw"));


            //课程简介
            sheet.getRow(4).getCell(0).setCellValue(biz.getString("kcjj"));
            // 教学难点
            sheet.getRow(6).getCell(0).setCellValue(biz.getString("jxnd"));
            //课程目标-感性目标
            sheet.getRow(8).getCell(1).setCellValue(biz.getString("gxmb"));
            //课程目标-感性目标
            sheet.getRow(9).getCell(1).setCellValue(biz.getString("lxmb"));

            //亮点经验  ,
            sheet.getRow(14).getCell(0).setCellValue(biz.getString("ldjy"));
            //案例概述
            sheet.getRow(16).getCell(0).setCellValue(biz.getString("algs"));

            //课程结构  //目的		//内容			//辅助工具	//培训时长（分钟）	//培训方法
            int structureIndex = 12;
            List<Map<String, Object>> courseStructuresList = (List<Map<String, Object>>) biz.getObject(structuresCode);

            if (CollectionUtils.isNotEmpty(courseStructuresList)) {

                Row structureRow;
                Cell structureCell;
                // 获取默认样式
                CellStyle structureStyle = sheet.getRow(structureIndex).getCell(0).getCellStyle();
                String[][] structureColCode = {{"zj", "0", "0"}, {"md", "1", "2"}, {"nr", "3", "5"}, {"fzgj", "6", "6"}, {"pxsc", "7", "7"}, {"pxff", "8", "9"}};

                sheet.shiftRows(structureIndex, sheet.getLastRowNum(), courseStructuresList.size(), true, false);

                for (Map<String, Object> m : courseStructuresList) {
                    structureRow = Optional.ofNullable(sheet.getRow(structureIndex)).orElse(sheet.createRow(structureIndex));

                    for (int i = 0; i < 10; i++) {
                        structureRow.createCell(i).setCellStyle(structureStyle);
                    }

                    for (int i = 0; i < structureColCode.length; i++) {
                        structureCell = structureRow.getCell(Integer.parseInt(structureColCode[i][1]));
                        structureCell.setCellValue(MapUtils.getString(m, structureColCode[i][0], ""));

                        if (!structureColCode[i][1].equals(structureColCode[i][2])) {
                            CellRangeAddress cellAddresses = new CellRangeAddress(structureIndex, structureIndex, Integer.parseInt(structureColCode[i][1]), Integer.parseInt(structureColCode[i][2]));
                            sheet.addMergedRegion(cellAddresses);
                        }

                    }
                    ++structureIndex;
                }
            }


            outputStream = new ByteArrayOutputStream();
            wb.write(outputStream);
            bytes = outputStream.toByteArray();

            log.info("生成文档成功:{}", biz.getId());

        } catch (Exception e) {
            log.info("======生成课程说明失败:{}", e);
        } finally {

            if (wb != null) {
                wb.close();
            }

            if (outputStream != null) {
                outputStream.close();
            }
            if (resourceAsStream != null) {
                resourceAsStream.close();
            }

        }

        return bytes;
    }


    @GetMapping("/downloadExamplesFile")
    public ResponseResult downloadExamplesFile(String exampleCode, HttpServletResponse response) throws Exception {
        BizObjectQueryModel queryModel = new BizObjectQueryModel();
        queryModel.setSchemaCode("examplesFile");
        queryModel.setQueryCode("examplesFile");
        queryModel.setFilterExpr(Q.it("exampleCode", FilterExpression.Op.Eq, exampleCode));
        queryModel.setPageable(new PageableImpl(0, 1));
        Page<BizObjectModel> page = getBizObjectFacade().queryBizObjects(queryModel);
        if (page.getTotal() == 0) {
            return getErrResponseResult(-1L, "示例文件不存在");
        }

        BizObjectModel bizObjectModel = page.getContent().get(0);
        List<Map<String, Object>> exampleFileList = (List<Map<String, Object>>) bizObjectModel.get("exampleFile");

        if (CollectionUtils.isEmpty(exampleFileList)) {
            return getErrResponseResult(-2L, "示例文件不存在");
        }

        Map<String, Object> map = exampleFileList.get(0);
        String refId = MapUtils.getString(map, "refId");

        if (StringUtils.isEmpty(refId)) {
            return getErrResponseResult(-3L, "示例文件不存在");
        }


        return getOkResponseResult(refId);

    }

    /**
     * 导入结果
     * <p>
     * 2022-04-13 结果确认与发布点击“导入结果”按钮导入的excel校验导入的编号。只要其中一条的编号不存在，整个文件不允许导入。
     *
     * @param file
     * @return
     * @throws Exception
     */
    @PostMapping("/importReviewResults2")
    public ResponseResult importReviewResults2(MultipartFile file) throws Exception {

        String fileName = file.getName();
        String fileExtension = fileName.substring(fileName.lastIndexOf(".") + 1);

        if (fileName.contains(".") && !StringUtils.equalsAny(fileExtension, new CharSequence[]{"xls", "xlsx"})) {
            log.info("文件格式错误,只支持xlsx或xls格式文件");
            return getErrResponseResult(-1l, "文件格式错误,只支持xlsx或xls格式文件");
        }

        Workbook wb = WorkbookFactory.create(file.getInputStream());

        Sheet sheet = wb.getSheetAt(0);
        Row firstRow = sheet.getRow(0);
        //课程id,课程名称,最终得分,评审等级(课程等级),可开放范围
        List<String> colCode = new ArrayList<>(Arrays.asList("id", "courseName", "zzdf", "kcdj", "kkffw"));
        List<String> colName = new ArrayList<>();
        for (int i = 0; i < colCode.size(); i++) {
            colName.add(FileHelper.getStringCellValue(firstRow.getCell(i)));
        }
        int lastRowNum = sheet.getLastRowNum();
        //取出导入数据
        List<Map<String, Object>> scoreList = new ArrayList<>();

        for (int i = 1; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            Map<String, Object> scoreMap = new HashMap<>();
            for (int j = 0; j < colCode.size(); j++) {
                Cell cell = row.getCell(j);
                String value = null;
                if ((cell == null || StringUtils.isEmpty(value = FileHelper.getStringCellValue(cell))) && colCode.size() - 1 > j) {
                    log.info("{}行{}列不能为空", i + 1, colName.get(j));
                    return getErrResponseResult(-1l, StrUtil.format("{}行{}列不能为空", i + 1, colName.get(j)));
                }

                scoreMap.put(colCode.get(j), value);
            }
            scoreMap.remove("courseName");
            scoreList.add(scoreMap);
        }

        String schemaCode = "kcsbNew";

        //判断课程存在
        List<String> collect = scoreList.stream().filter(m -> {
            boolean exists = getBizObjectFacade().existsBizObject(schemaCode, MapUtils.getString(m, "id", ""));
            return !exists;
        }).map(m -> MapUtils.getString(m, "id", "")).collect(Collectors.toList());

        if (CollectionUtils.isNotEmpty(collect)) {
            return getErrResponseResult(-1l, StrUtil.format("导入失败:课程id:{} ,不存在课程数据", StringUtils.join(collect, ",")));
        }

        for (int i = 0; i < scoreList.size(); i++) {
            Object dept = scoreList.get(i).get("kkffw");

            if (ObjectUtils.isEmpty(dept)) {
                continue;
            }

            String[] dpetList = dept.toString().split(";");
            List<SelectionValue> valueList = new ArrayList<>();
            for (int j = 0; j < dpetList.length; j++) {
                SelectionValue importDept = getImportDept(dpetList[j]);
                if (importDept == null) {
                    return getErrResponseResult(-1l, StrUtil.format("导入失败:多选部门{} 不存在", dpetList[j]));
                }
                valueList.add(importDept);
            }

            scoreList.get(i).put("kkffw", valueList);

        }


        //修改课程表
        List<BizObjectModel> kcsbList = scoreList.stream()
                .map(m -> new BizObjectCreatedModel("kcsbNew", m, false)).collect(Collectors.toList());

        //getBizObjectFacade().batchSaveBizObjectModel(getUserId(),kcsbList, DefaultPropertyType.ID.getCode());
        BizObjectFacade bizObjectFacade = getBizObjectFacade();
        String kcsbNewTableName = bizObjectFacade.getTableName("kcsbNew");
        //写update语句更改处于可更改范围的课程。
        for (BizObjectModel bizObjectModel : kcsbList) {
            StringBuilder upsql = new StringBuilder();
            String bizObjectId = (String) bizObjectModel.get("id");//where条件
            String zzdf = (String) bizObjectModel.get("zzdf");//更新最终得分
            String kcdj = (String) bizObjectModel.get("kcdj");//更新课程等级？还是推荐结果
            //String kkffw = (String) bizObjectModel.get("kkffw");//部门开放范围暂不更新
            String updateSql = "update " + kcsbNewTableName + " set zzdf = '" + zzdf + "'" +
                    ",kcdj = '" + kcdj +
                    "' where id = '" + bizObjectId + "' and kcbh != '' and (kcbh != '' or kcbh is not null)";
            int i = entityManager.createNativeQuery(updateSql).executeUpdate();
            log.info("修改数据:{}条", i);
        }

        return getOkResponseResult("成功");
    }

    @Autowired
    DepartmentService departmentService;


    private SelectionValue getImportDept(String deptName) {

        if (StringUtils.isEmpty(deptName)) {
            return null;
        }

        String[] split = deptName.split("/");

        Department dept = new Department();
        for (int i = 0; i < split.length; i++) {
            List<Department> list = departmentService.getDeptByDepartmentNameAndParentId(split[i], dept.getId());
            if (CollectionUtils.isEmpty(list)) {
                return null;
            }
            dept = list.get(0);
        }

        SelectionValue selectionValue = new SelectionValue();

        selectionValue.setType(selectionValue.getType());
        selectionValue.setId(selectionValue.getId());
        selectionValue.setName(selectionValue.getName());

        return selectionValue;
    }

    /**
     * 导入基层评委得分
     *
     * @param file
     * @return
     * @throws Exception
     */
    @PostMapping("/importReviewResults")
    public ResponseResult importReviewResults(MultipartFile file) throws Exception {

        String fileName = file.getName();

        //课程id,课程名称,基层评委得分,推荐等级
        List<String> colCode = new ArrayList<>(Arrays.asList("id", "courseName", "jcpsdf", "tjjg"));

        List<Map<String, Object>> scoreList = ExcelUtils.excelParse(file.getInputStream(), fileName, colCode);

        String schemaCode = SchemaUtils.courseApply;

        //判断课程存在
        List<String> collect = scoreList.stream().filter(m -> {
            boolean exists = getBizObjectFacade().existsBizObject(schemaCode, MapUtils.getString(m, "id", ""));
            return !exists;

        }).map(m -> MapUtils.getString(m, "id", "")).collect(Collectors.toList());

        if (CollectionUtils.isNotEmpty(collect)) {
            return getErrResponseResult(-1l, StrUtil.format("导入失败:课程id:{} ,不存在课程数据", StringUtils.join(collect, ",")));
        }

        //修改课程表
        List<BizObjectModel> kcsbList = scoreList.stream()
                .map(m -> {
                    m.remove("courseName");
                    return new BizObjectCreatedModel(schemaCode, m, false);
                }).collect(Collectors.toList());

        getBizObjectFacade().batchSaveBizObjectModel(getUserId(), kcsbList, DefaultPropertyType.ID.getCode());

        return getOkResponseResult("成功");
    }


    /**
     * 导出评审评分
     *
     * @param response
     */
    @PostMapping("/exportScore")
    public void exportScore(@RequestBody Map<String, Object> map, HttpServletResponse response) {

        List<String> bizId = (List<String>) map.get("bizId");
        String type = (String) map.get("type");

        if (!ArrayUtils.contains(new Object[]{"jcpstj", "lrcpgl", "zpgl"}, type)) {
//            return getErrResponseResult(-1l,"业务类型 type 不是 基层评审推荐 或 中心初评 或 中心终评");
            throw new RuntimeException("业务类型 type 不是 基层评审推荐 或 中心初评 或 中心终评");
        }


        if (CollectionUtils.isEmpty(bizId)) {
//            return getErrResponseResult(-1L, "参数bizId不能为空");
            throw new RuntimeException("参数bizId不能为空");
        }

        BizObjectQueryModel queryModel = new BizObjectQueryModel();
        queryModel.setSchemaCode(SchemaUtils.courseScore);
        queryModel.setQueryCode(SchemaUtils.courseScore_query_all);

        queryModel.setPageable(new PageableImpl(0, 1000));
        List<FilterExpression> filterList = new ArrayList<>();
        filterList.add(Q.it("glsbd", FilterExpression.Op.In, bizId));
        filterList.add(Q.it("dqyw", FilterExpression.Op.Eq, type));
        FilterExpression.And and = Q.and(filterList);
        queryModel.setFilterExpr(and);

        Page<BizObjectModel> page = getBizObjectFacade().queryBizObjects(queryModel);

        //不校验评分是否存在
//        if (page.getTotal() == 0) {
//            throw new RuntimeException(String.format("没有评分数据,当前业务:%s,bizId:%s", type, StringUtils.join(bizId, ",")));
////            return getErrResponseResult(-1L, String.format("没有评分数据,当前业务:%s,bizId:%s", type,StringUtils.join(bizId,",")));
//        }

        List<BizObjectCreatedModel> bizList = page.getContent().stream().map(biz -> {

            Map glsbd = biz.getMap("glsbd");
            biz.put("glsbd", MapUtils.getString(glsbd, "id"));
            biz.put("courseName", MapUtils.getString(glsbd, MapUtils.getString(glsbd, "displayCode")));
            return new BizObjectCreatedModel(biz.getSchemaCode(), biz.getData(), false);
        }).collect(Collectors.toList());


        exportData(bizList, bizId, type, response);

//        return getOkResponseResult("成功");
    }

    /**
     * 导出execl
     */
    public void exportData(List<BizObjectCreatedModel> bizList, List<String> bizId, String type, HttpServletResponse response) {
        String fileName = "课程评分-" + System.currentTimeMillis() + ".xlsx";

        try {
            SXSSFWorkbook wb = getSheetInfo(bizList, bizId, type);

            response.reset();
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
            response.setHeader("Access-Control-Allow-Origin", "*");
            OutputStream os = response.getOutputStream();
            wb.write(os);
            wb.close();
            os.flush();
            os.close();

        } catch (Exception e) {
            log.error("", e);
            throw new RuntimeException("系统异常:".concat(e.getMessage()));
        }
    }


    /**
     * 获取到评审数据
     * 评分分类:  等级,分数
     */
    public SXSSFWorkbook getSheetInfo(List<BizObjectCreatedModel> bizList, List<String> bizIdList, String type) {
        //--分类
        //  -- 课程
        //    -- 评委评审表
        Map<String, Map<String, List<BizObjectCreatedModel>>> categoryMap = bizList.stream()
                .collect(Collectors.groupingBy(biz -> "等级".equals(biz.getString("lx")) ? "grade" : "score",
                        Collectors.groupingBy(b -> b.getString("glsbd"))));

        //分数评审的课程
        Map<String, List<BizObjectCreatedModel>> scoreMap = categoryMap.get("score");
        //等级评审的课程
        Map<String, List<BizObjectCreatedModel>> gradeMap = categoryMap.get("grade");

        Map<String, List<List<String>>> map = new HashMap<>();


        List<String> kcIdList = new ArrayList<>();
        BizObjectFacade bizObjectFacade = getBizObjectFacade();
        List<List<String>> scoreSheetInfo = new ArrayList<List<String>>();
        List<List<String>> gradeSheetInfo = new ArrayList<List<String>>();
        if (MapUtils.isNotEmpty(scoreMap)) {
            scoreSheetInfo = toParseScoreList(scoreMap);
            for (int i = 1; i < scoreSheetInfo.size(); i++) {
                kcIdList.add(scoreSheetInfo.get(i).get(0));
            }
            List<String> collect = bizIdList.stream().filter(a -> !kcIdList.contains(a)).collect(Collectors.toList());
            for (String s : collect) {
                BizObjectCreatedModel kcsbNew = bizObjectFacade.getBizObject("kcsbNew", s);
                String courseName = StringUtils.isBlank((String) kcsbNew.get("courseName")) ? "" : (String) kcsbNew.get("courseName");
                List<String> list = new ArrayList<>();
                list.add(s);
                list.add(courseName);
                scoreSheetInfo.add(list);
            }
            map.put("分数-课程评审", scoreSheetInfo);
        } else {
            log.info("分数类型评分无");
        }

        if (MapUtils.isNotEmpty(gradeMap)) {
            gradeSheetInfo = toParseGrade(gradeMap);
            for (int i = 1; i < gradeSheetInfo.size(); i++) {
                kcIdList.add(gradeSheetInfo.get(i).get(1));
            }
            List<String> collect = bizIdList.stream().filter(a -> !kcIdList.contains(a)).collect(Collectors.toList());
            for (String s : collect) {
                BizObjectCreatedModel kcsbNew = bizObjectFacade.getBizObject("kcsbNew", s);
                String courseName = StringUtils.isBlank((String) kcsbNew.get("courseName")) ? "" : (String) kcsbNew.get("courseName");
                List<String> list = new ArrayList<>();
                list.add(courseName);
                list.add(s);
                gradeSheetInfo.add(list);
            }
            map.put("等级-课程评审", gradeSheetInfo);
        } else {
            log.info("等级类型评分无");
        }
        if (scoreMap == null && gradeMap == null) {
            if ("jcpstj".equals(type)) {
                List<String> title = new ArrayList<>();
                title.add("课程id");
                title.add("课程名称");
                scoreSheetInfo.add(title);
                for (String s : bizIdList) {
                    BizObjectCreatedModel kcsbNew = bizObjectFacade.getBizObject("kcsbNew", s);
                    String courseName = StringUtils.isBlank((String) kcsbNew.get("courseName")) ? "" : (String) kcsbNew.get("courseName");
                    List<String> list = new ArrayList<>();
                    list.add(s);
                    list.add(courseName);
                    scoreSheetInfo.add(list);
                }
                map.put("分数-课程评审", scoreSheetInfo);
            }
            if ("lrcpgl".equals(type)) {
                List<String> title = new ArrayList<>();
                title.add("课程");
                title.add("课程ID");
                gradeSheetInfo.add(title);
                for (String s : bizIdList) {
                    BizObjectCreatedModel kcsbNew = bizObjectFacade.getBizObject("kcsbNew", s);
                    String courseName = StringUtils.isBlank((String) kcsbNew.get("courseName")) ? "" : (String) kcsbNew.get("courseName");
                    List<String> list = new ArrayList<>();
                    list.add(courseName);
                    list.add(s);
                    gradeSheetInfo.add(list);
                    map.put("等级-课程评审", gradeSheetInfo);
                }

            }
            if ("zpgl".equals(type)) {
                List<String> title = new ArrayList<>();
                title.add("课程id");
                title.add("课程名称");
                scoreSheetInfo.add(title);
                for (String s : bizIdList) {
                    BizObjectCreatedModel kcsbNew = bizObjectFacade.getBizObject("kcsbNew", s);
                    String courseName = StringUtils.isBlank((String) kcsbNew.get("courseName")) ? "" : (String) kcsbNew.get("courseName");
                    List<String> list = new ArrayList<>();
                    list.add(s);
                    list.add(courseName);
                    scoreSheetInfo.add(list);
                    map.put("分数-课程评审", scoreSheetInfo);
                }

            }
        }

        SXSSFWorkbook workBook = createWorkBook(map);

        return workBook;


    }

    /**
     * 所有课程评委都一致,且评分维度都一致的课程才能走这个批量处理的逻辑
     * <p>
     * 1课程分组
     * 课程	评委	项点	得分
     * A	zs	a	1
     * A	ls	a	2
     * A	zs	b	3
     * A	ls	b	4
     * 2.项点分组
     * 项   课   评  分
     * a	A	zs	1
     * A	ls	2
     * b	A	zs	3
     * A	ls	4
     * 3.评委行专列
     * 项点 zs  ls  课
     * a	1	2   A
     * b	3	4   A
     * <p>
     * 4.项点组 行转列
     * 项a zs  ls  项b  zs  ls   课
     * a	1	2	b	3	4    A
     *
     * @param gradeMap
     */
    private List<List<String>> toParseGrade(Map<String, List<BizObjectCreatedModel>> gradeMap) {
        //1课程分组->2项点分组->评委行转列->项点行转列

        List<String> header = new ArrayList<>();
        //获取评委名称
        List<String> pwList = new ArrayList<>();
        //获取项点
        List<String> itemList = new ArrayList<>();


        List<List<String>> sheetDataInfo = gradeMap.entrySet().stream().map(entry -> {
            //评委评列表
            List<BizObjectCreatedModel> courseList = entry.getValue();
            if (CollectionUtils.isEmpty(pwList)) {
                pwList.addAll(courseList.stream().map(biz -> biz.getCreater().getName()).distinct().collect(Collectors.toList()));
            }


            //2.获取所有评委项点明细,并根据项点分组
            Map<String, List<Map<String, Object>>> xdGroup = courseList.stream().flatMap(biz -> {
                //评分明细
                List<Map<String, Object>> detailList = (List<Map<String, Object>>) biz.get("kcpfzb");

                if (CollectionUtils.isEmpty(itemList)) {
                    itemList.addAll(detailList.stream().map(m -> MapUtils.getString(m, "xd")).collect(Collectors.toList()));
                }

                return detailList.stream().map(m -> {
                    Map<String, Object> listMap = new HashMap<>();
                    listMap.put("dj", m.get("dj"));
                    listMap.put("xd", m.get("xd"));
                    listMap.put("pw", biz.getCreater().getName());

                    return listMap;
                });

            }).collect(Collectors.groupingBy(m -> MapUtils.getString(m, "xd")));

            //3.项点分组里的评委行转列
            Map<String, Map<String, String>> itemMap = rowToCol(xdGroup);


            //4.项点组 行转列
            BizObjectCreatedModel courseBiz = entry.getValue().get(0);

            List<String> eList = new ArrayList<>();
            eList.add(courseBiz.getString("courseName"));
            eList.add(courseBiz.getString("glsbd"));

            for (int i = 0; i < itemList.size(); i++) {
                eList.add(itemList.get(i));
                Map<String, String> items = itemMap.get(itemList.get(i));
                for (int j = 0; j < pwList.size(); j++) {
                    eList.add(items.get(pwList.get(j)));
                }
            }

            return eList;

        }).collect(Collectors.toList());

        if (!CollectionUtils.isEmpty(sheetDataInfo)) {
            header.add("课程");
            header.add("课程ID");

            for (int i = 0; i < itemList.size(); i++) {
                header.add("项点");
                for (int j = 0; j < pwList.size(); j++) {
                    header.add(pwList.get(j));
                }
            }
            //补充表头
            sheetDataInfo.add(0, header);
        }
        return sheetDataInfo;


    }


    // 等级评委评审,评委 行转列
    private Map<String, Map<String, String>> rowToCol(Map<String, List<Map<String, Object>>> xdGroup) {

        Map<String, Map<String, String>> itemList = xdGroup.entrySet().stream().collect(Collectors.toMap(xdEntry -> xdEntry.getKey(),
                xdEntry -> {
                    List<Map<String, Object>> xdEntryValue = xdEntry.getValue();
                    return xdEntryValue.stream().collect(Collectors.toMap(xditem -> MapUtils.getString(xditem, "pw"),
                            xditem -> MapUtils.getString(xditem, "dj"),
                            (k1, k2) -> k1));
                }, (k1, k2) -> k1));
        return itemList;
    }

    /**
     * 获取各课程各评委总分数视图
     *
     * @param scoreMap
     * @return
     */
    private List<List<String>> toParseScoreList(Map<String, List<BizObjectCreatedModel>> scoreMap) {

        Set<String> pwmc = new HashSet<>();
        List<Map<String, Object>> collect = scoreMap.entrySet().stream().map(entry -> {
            List<BizObjectCreatedModel> courseList = entry.getValue();
            //一个课程的各评审员得分汇总
            Map<String, Integer> score = courseList.stream().collect(Collectors.toMap(biz -> biz.getCreater().getName(),
                    biz -> MapUtils.getInteger(biz.getData(), "sumdf", 0), (k1, k2) -> k1));

            pwmc.addAll(score.keySet());

            Map<String, Object> map = new HashMap<>();
            BizObjectCreatedModel bizObjectCreatedModel = courseList.get(0);
            map.put("glsbd", bizObjectCreatedModel.getString("glsbd"));
            map.put("courseName", bizObjectCreatedModel.getString("courseName"));
            map.put("scoreMap", score);
            return map;
        }).collect(Collectors.toList());


        //得分结果
        List<List<String>> scoreList = new ArrayList<>();
        List<String> scoreRows = new ArrayList<>();

        scoreRows.add("课程id");
        scoreRows.add("课程名称");
        scoreRows.addAll(pwmc);
        scoreList.add(scoreRows);

        int len = scoreRows.size();

        collect.forEach(m -> {

            List<String> list = new ArrayList<>();
            list.add(MapUtils.getString(m, "glsbd", ""));
            list.add(MapUtils.getString(m, "courseName", ""));
            Map<String, Object> score = (Map<String, Object>) m.get("scoreMap");
            for (int i = 2; i < len; i++) {
                list.add(MapUtils.getString(score, scoreRows.get(i), ""));
            }
            scoreList.add(list);
        });

        return scoreList;
    }


    private SXSSFWorkbook createWorkBook(Map<String, List<List<String>>> map) {
        SXSSFWorkbook wb = new SXSSFWorkbook();

        map.forEach((key, value) -> {
            try {
                createSheet(value, key, wb);
            } catch (Exception e) {
                e.printStackTrace();
            }
        });

        return wb;
    }


    private void createSheet(List<List<String>> list, String sheetName, SXSSFWorkbook wb) {

        SXSSFSheet sheet = wb.createSheet(sheetName);

        CellStyle headStyle = wb.createCellStyle();
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 13);
        headStyle.setFont(font);
        //sheet.setDefaultColumnWidth(25);
        sheet.setDefaultColumnWidth(40);

        for (int i = 0; i < list.size(); i++) {
            SXSSFRow row = sheet.createRow(i);

            for (int j = 0; j < list.get(i).size(); j++) {
                SXSSFCell firstCell = row.createCell(j);
                if (i == 0) {
                    firstCell.setCellStyle(headStyle);
                }
                firstCell.setCellValue(list.get(i).get(j));
            }

        }

    }


}
